﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using DevComponents.DotNetBar.Metro;
using System.Data.SqlClient;
using System.Data.Odbc;
using Excel_12 = Microsoft.Office.Interop.Excel;

namespace QLBH_N6
{
    public partial class Form1 : DevComponents.DotNetBar.Metro.MetroForm
    {
        SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=QLBH;Integrated Security=True");
        string manv; // đây là mã nv lấy từ form đăng nhập, dùng được luôn
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            conn.Open();
            
            autocomplete();
            // TODO: This line of code loads data into the 'QLBHDataSet.HangHoa' table. You can move, or remove it, as needed.
            // TODO: This line of code loads data into the 'QLBHDataSet2.Uudai' table. You can move, or remove it, as needed.
            // TODO: This line of code loads data into the 'QLBHDataSet.CTPhieuNhap' table. You can move, or remove it, as needed.
            Open();

        }
        public void autocomplete()
        {
            AutoCompleteStringCollection auto = new AutoCompleteStringCollection();

            SqlCommand sqlcmd = new SqlCommand();
            sqlcmd.Connection = conn;
            sqlcmd.CommandType = CommandType.Text;
            sqlcmd.CommandText = "select TenHang from HangHoa";

            SqlDataReader reader = sqlcmd.ExecuteReader();
            if (reader != null)
            {
                while (reader.Read())
                {
                    auto.Add(reader["TenHang"].ToString());
                }
            }

            nh_th.AutoCompleteMode = AutoCompleteMode.Suggest;
            nh_th.AutoCompleteSource = AutoCompleteSource.CustomSource;
            nh_th.AutoCompleteCustomSource = auto;

            bh_th.AutoCompleteMode = AutoCompleteMode.Suggest;
            bh_th.AutoCompleteSource = AutoCompleteSource.CustomSource;
            bh_th.AutoCompleteCustomSource = auto;

            hdbh_th.AutoCompleteMode = AutoCompleteMode.Suggest;
            hdbh_th.AutoCompleteSource = AutoCompleteSource.CustomSource;
            hdbh_th.AutoCompleteCustomSource = auto;

            hdnh_th.AutoCompleteMode = AutoCompleteMode.Suggest;
            hdnh_th.AutoCompleteSource = AutoCompleteSource.CustomSource;
            hdnh_th.AutoCompleteCustomSource = auto;
        }
        public void data(string a)
        {
            manv = a;
        }
        DataView dvhh;
        DataView dvbc;
        DataView dvbh;
        
        public void Open()
        {
            bc_date1.Value = DateTime.Now;
            bc_date2.Value = DateTime.Now;
            SqlDataAdapter datahh = new SqlDataAdapter("select MaHang,TenHang,DonVi,SoLuong,GiaNhap,GiaBan,DiaDiem,TenDV,HangHoa.KhoSo,DV.MaDV from HangHoa,DV,Kho where DV.MaDV=HangHoa.DVCC and HangHoa.KhoSo=Kho.KhoSo", conn);
            DataTable dthh = new DataTable();
            datahh.Fill(dthh);
            dvhh = new DataView(dthh);
            hh_data.DataSource = dvhh;

            SqlDataAdapter datanh = new SqlDataAdapter("select CTPhieuNhap.MaHang,TenHang,DonVi,CTPhieuNhap.SoLuong,GiaNhap,DV.MaDV,TenDV,Ten,DiaDiem,CTPhieuNhap.MaPhieu,NgayNhap,CTPhieuNhap.ThanhTien,TongGTN from PhieuNhap,CTPhieuNhap,NhanVien,DV,HangHoa,Kho where Kho.KhoSo = PhieuNhap.KhoSo and HangHoa.MaHang = CTPhieuNhap.MaHang and PhieuNhap.MaDV = DV.MaDV and NhanVien.MaNV = PhieuNhap.MaNV and CTPhieuNhap.MaPhieu = PhieuNhap.MaPhieu", conn);
            DataTable dtnh = new DataTable();
            datanh.Fill(dtnh);
            DataView dvnh = new DataView(dtnh);
            nh_data.DataSource = dvnh;
            nh_nn.Value = DateTime.Now;
            

            SqlDataAdapter dataxh = new SqlDataAdapter("Select Ck,PhieuXuat.ThanhTien,CTPhieuXuat.ThanhTien,TongGTX,MaKH,NgayXuat,PhieuXuat.MaPhieu,HangHoa.MaHang,TenHang,DonVi,CTPhieuXuat.SoLuong,CTPhieuXuat.DonGia,Kho.DiaDiem From HangHoa,PhieuXuat,CTPhieuXuat,Kho where PhieuXuat.MaPhieu=CTPhieuXuat.MaPhieu and Kho.KhoSo=HangHoa.KhoSo and HangHoa.MaHang=CTPhieuXuat.MaHang", conn);
            DataTable dtxh = new DataTable();
            dataxh.Fill(dtxh);
            DataView dvxh = new DataView(dtxh);
            bh_data.DataSource = dvxh;
            bh_nb.Value = DateTime.Now;
            dvbh = new DataView(dtxh);

            SqlDataAdapter datak = new SqlDataAdapter("select * from Kho", conn);
            DataTable dtk = new DataTable();
            datak.Fill(dtk);
            DataView dvk = new DataView(dtk);
            k_data.DataSource = dvk;

            hh_k.DisplayMember = "KhoSo";
            hh_k.DataSource = dtk;

            nh_k.DisplayMember = "KhoSo";
            nh_k.DataSource = dtk;
            hdnh_k.DisplayMember = "DiaDiem";
            hdnh_k.ValueMember = "KhoSo";
            hdnh_k.DataSource = dtk;

            SqlDataAdapter datanv = new SqlDataAdapter("select * from NhanVien", conn);
            DataTable dtnv = new DataTable();
            datanv.Fill(dtnv);
            DataView dvnv = new DataView(dtnv);
            nv_data.DataSource = dvnv;

            SqlDataAdapter datakh = new SqlDataAdapter("select * from KH", conn);
            DataTable dtkh = new DataTable();
            datakh.Fill(dtkh);
            DataView dvkh = new DataView(dtkh);
            kh_data.DataSource = dvkh;

            SqlDataAdapter datancc = new SqlDataAdapter("select * from DV", conn);
            DataTable dtncc = new DataTable();
            datancc.Fill(dtncc);
            DataView dvncc = new DataView(dtncc);
            ncc_data.DataSource = dvncc;
            

            SqlDataAdapter datadvhh = new SqlDataAdapter("select distinct donvi from hanghoa", conn);
            DataTable dtdvhh= new DataTable();
            datadvhh.Fill(dtdvhh);
            
            hh_data.DataSource = dvhh;
            nh_ncc.DataSource = dtncc;
            nh_ncc.DisplayMember = "TenDV";
            nh_ncc.ValueMember = "MaDV";

            hdnh_ncc.DataSource = dtncc;
            hdnh_ncc.DisplayMember = "TenDV";
            hdnh_ncc.ValueMember = "MaDV";

            hh_ncc.DisplayMember = "MaDV";
            hh_ncc.DataSource = dtncc;

            nh_dv.DataSource = dtdvhh;
            nh_dv.DisplayMember = "DonVi";

            hh_dv.DataSource = dtdvhh;
            hh_dv.DisplayMember = "Donvi";

            hdnh_dv.DataSource = dtdvhh;
            hdnh_dv.DisplayMember = "DonVi";
            hdnh_dv.ValueMember = "DonVi";
        }

        public void ClearHH()
        {
            hh_mh.Clear();
            hh_th.Clear();
            hh_dv.ResetText();
            hh_sl.Clear();
            hh_gn.Clear();
            hh_gb.Clear();
            hh_ncc.ResetText();
            hh_k.ResetText();
        }

        public void ClearHDBH()
        {
            hdbh_sl.Text = "0";
            hdbh_dv.Text = "";
            hdbh_dg.Clear();
            hdbh_mh.Clear();
            hdbh_th.Clear();
            hdbh_sltk.Text = "0";
            hdbh_tt.Clear();
        }
        public void ClearK()
        {
            k_k.Clear();
            k_dck.Clear();
        }

        public void ClearNV()
        {
            nv_mnv.Clear();
            nv_tnv.Clear();
            nv_sdt.Clear();
            nv_dc.Clear();
        }

        public void ClearKH()
        {
            kh_mkh.Clear();
            kh_tkh.Clear();
            kh_sdt.Clear();
            kh_dc.Clear();
        }

        public void ClearBH()
        {
            bh_dv.Text = "";
            bh_gb.Clear();
            bh_mh.Clear();
            bh_th.Clear();
            bh_sl.Clear();
            bh_tt.Clear();
        }

        public void ClearNH()
        {
            
            nh_mh.Clear();
            nh_th.Clear();
            nh_gn.Clear();
            nh_dv.Text = "";
            nh_sl.Clear();
            nh_tt.Clear();
            
        }
        public void ClearHDNH()
        {

            hdnh_mh.Clear();
            hdnh_th.Clear();
            hdnh_gn.Clear();
            hdnh_dv.Text = "";
            hdnh_sl.Clear();
            hdnh_tt.Clear();

        }
        public void ClearNCC()
        {
            ncc_MaDV.Clear();
            ncc_TenDV.Clear();
            ncc_SDT.Clear();
            ncc_DC.Clear();
        }
        private void buttonX1_Click(object sender, EventArgs e)
        {
            SqlCommand cmd = new SqlCommand("SuaHang",conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@MaHang", hh_mh.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@SoLuong", hh_sl.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@DonVi", hh_dv.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@KhoSo", hh_k.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@GiaBan", hh_gb.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaDV", hh_ncc.Text);
            cmd.Parameters.Add(p);

            int count = cmd.ExecuteNonQuery();
            if (count > 0)
            {
                Open();
            }

            hh_mh.Enabled = true;
            hh_th.Enabled = true;
            hh_gn.Enabled = true;
            hh_dv.Enabled = true;
            hh_ncc.Enabled = true;

            ClearHH();

        }

        private void buttonX2_Click(object sender, EventArgs e)
        {
            SqlCommand cmd = new SqlCommand("XoaHang", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@MaHang", hh_mh.Text);
            cmd.Parameters.Add(p);

            int count = cmd.ExecuteNonQuery();
            if (count > 0)
            {
                Open();
            }

            ClearHH();
        }

        private void hh_sl_KeyPress(object sender, KeyPressEventArgs e)
        {
            e.Handled = !((e.KeyChar >= 48) && (e.KeyChar <= 57) || e.KeyChar == 8);
        }

        private void hh_gn_KeyPress(object sender, KeyPressEventArgs e)
        {
            e.Handled = !((e.KeyChar >= 48) && (e.KeyChar <= 57) || e.KeyChar == 8 || e.KeyChar == 46);
        }

        private void hh_gb_KeyPress(object sender, KeyPressEventArgs e)
        {
            e.Handled = !((e.KeyChar >= 48) && (e.KeyChar <= 57) || e.KeyChar == 8 || e.KeyChar == 46);
        }

        private void hh_data_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            hh_mh.Enabled = false;
            hh_th.Enabled = false;
            hh_gn.Enabled = false;       

            hh_mh.Text = hh_data.CurrentRow.Cells[0].Value.ToString();
            hh_th.Text = hh_data.CurrentRow.Cells[1].Value.ToString();
            hh_dv.Text = hh_data.CurrentRow.Cells[2].Value.ToString();
            hh_sl.Text = hh_data.CurrentRow.Cells[3].Value.ToString();
            hh_gn.Text = hh_data.CurrentRow.Cells[4].Value.ToString();
            hh_gb.Text = hh_data.CurrentRow.Cells[5].Value.ToString();
            hh_k.Text = hh_data.CurrentRow.Cells[8].Value.ToString();
            hh_ncc.Text = hh_data.CurrentRow.Cells[9].Value.ToString();
        }

        private void nh_gn_KeyPress(object sender, KeyPressEventArgs e)
        {
            e.Handled = !((e.KeyChar >= 48) && (e.KeyChar <= 57) || e.KeyChar == 8 || e.KeyChar == 46);
        }

        private void nh_sl_KeyPress(object sender, KeyPressEventArgs e)
        {
            e.Handled = !((e.KeyChar >= 48) && (e.KeyChar <= 57) || e.KeyChar == 8);
        }

        private void nh_them_Click(object sender, EventArgs e)
        {
            nh_mh.Enabled = true;
            nh_th.Enabled = false;
            for (int i = 0; i < nh_data.RowCount; i++)
            {
                if (nh_mh.Text == nh_data.Rows[i].Cells["MaHangNhap"].Value.ToString())
                {
                    if (nh_mp.Text == nh_data.Rows[i].Cells["MaPhieuNhap"].Value.ToString())
                    {
                        MessageBox.Show("Mã hàng này đã tồn tại trong phiếu nhập này!");
                        return;
                    }
                }
            }
            nh_ncc.Enabled = false;
            nh_k.Enabled = false;

            SqlCommand cmd = new SqlCommand("nhaphang", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@MaPhieu", nh_mp.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaDV", nh_ncc.SelectedValue.ToString());
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaNV", manv);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@NgayNhap", nh_nn.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaHang", nh_mh.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@SoLuong", nh_sl.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@DonGia", nh_gn.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@Kho", nh_k.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@TenHang", nh_th.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@DonVi", nh_dv.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@DVCC", nh_ncc.Text);
            cmd.Parameters.Add(p);

            int count = cmd.ExecuteNonQuery();
            if (count > 0)
            {
                Open();
                MessageBox.Show("Them thanh cong");
            }

            for (int i = 0; i < nh_data.Rows.Count; i++)
                if (nh_data.Rows[i].Cells["MaPhieuNhap"].Value.ToString() == nh_mp.Text)
                {
                    nh_tonggt.Text = nh_data.Rows[i].Cells["TongGTN"].Value.ToString();
                    break;
                }
            ClearNH();
        }

        private void k_data_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            k_them.Enabled = false;
            k_k.Text = k_data.CurrentRow.Cells[0].Value.ToString();
            k_dck.Text = k_data.CurrentRow.Cells[1].Value.ToString();
        }

        private void buttonX5_Click(object sender, EventArgs e)
        {
            k_them.Enabled = false;
            for (int i = 0; i < k_data.RowCount - 1; i++)
            {
                if (int.Parse(k_k.Text) == int.Parse(k_data.Rows[i].Cells[0].Value.ToString()))
                {
                    MessageBox.Show("Mã kho đã tồn tại");
                    return;
                }
            }
            SqlCommand cmd = new SqlCommand("ThemKho", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@MaKho", int.Parse(k_k.Text));
            cmd.Parameters.Add(p);
            p = new SqlParameter("@DiaDiem", k_dck.Text);
            cmd.Parameters.Add(p);

            int count = cmd.ExecuteNonQuery();
            if (count > 0)
            {
                Open();
                MessageBox.Show("Them thanh cong");
            }

            ClearK();
        }

        private void k_k_KeyPress(object sender, KeyPressEventArgs e)
        {
            e.Handled = !((e.KeyChar >= 48) && (e.KeyChar <= 57) || e.KeyChar == 8);
        }

        private void buttonX4_Click(object sender, EventArgs e)
        {

            SqlCommand cmd = new SqlCommand("SuaKho", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@KhoSo", int.Parse(k_k.Text));
            cmd.Parameters.Add(p);
            p = new SqlParameter("@DiaDiem", k_dck.Text);
            cmd.Parameters.Add(p);

            int count = cmd.ExecuteNonQuery();
            if (count > 0)
            {
                Open();
                MessageBox.Show("Sua thanh cong");
            }

            ClearK();
        }

        private void buttonX3_Click(object sender, EventArgs e)
        {
            SqlCommand cmd = new SqlCommand("XoaKho", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@KhoSo", int.Parse(k_k.Text));
            cmd.Parameters.Add(p);

            int count = cmd.ExecuteNonQuery();
            if (count > 0)
            {
                Open();
                MessageBox.Show("Xoa thanh cong");
            }

            ClearK();
        }

        private void textBoxX3_KeyPress(object sender, KeyPressEventArgs e)
        {
            e.Handled = !((e.KeyChar >= 48) && (e.KeyChar <= 57) || e.KeyChar == 8);
        }

        private void nv_them_Click(object sender, EventArgs e)
        {
            nv_them.Enabled = false;
            for (int i = 0; i < nv_data.RowCount - 1; i++)
            {
                if (nv_mnv.Text.Equals(nv_data.Rows[i].Cells[0].Value.ToString()))
                {
                    MessageBox.Show("Mã nhân viên đã tồn tại");
                    return;
                }
            }
            if (nv_sdt.TextLength > 11)
            {
                MessageBox.Show("Số điện thoại chỉ có ít hơn hoặc bằng 11 số");
                return;
            }
            SqlCommand cmd = new SqlCommand("ThemNV", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@MaNV", nv_mnv.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@Ten", nv_tnv.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@SDT", nv_sdt.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@DC", nv_dc.Text);
            cmd.Parameters.Add(p);

            int count = cmd.ExecuteNonQuery();
            if (count > 0)
            {
                Open();
                MessageBox.Show("Them thanh cong");
            }

            ClearNV();
        }

        private void nv_sua_Click(object sender, EventArgs e)
        {
            if (nv_sdt.TextLength > 11)
            {
                MessageBox.Show("Số điện thoại chỉ có ít hơn hoặc bằng 11 số");
                return;
            }
            SqlCommand cmd = new SqlCommand("SuaTTNV", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@MaNV", nv_mnv.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@TenNV", nv_tnv.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@SDT", nv_sdt.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@DC", nv_dc.Text);
            cmd.Parameters.Add(p);

            int count = cmd.ExecuteNonQuery();
            if (count > 0)
            {
                Open();
                MessageBox.Show("Sua thanh cong");
            }

            ClearNV();
        }

        private void nv_xoa_Click(object sender, EventArgs e)
        {
            SqlCommand cmd = new SqlCommand("XoaTTNV", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@MaNV", nv_mnv.Text);
            cmd.Parameters.Add(p);

            int count = cmd.ExecuteNonQuery();
            if (count > 0)
            {
                Open();
                MessageBox.Show("Xoa thanh cong");
            }

            ClearNV();
        }

        private void nv_data_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            nv_them.Enabled = false;
            nv_mnv.Text = nv_data.CurrentRow.Cells[0].Value.ToString();
            nv_tnv.Text = nv_data.CurrentRow.Cells[1].Value.ToString();
            nv_sdt.Text = nv_data.CurrentRow.Cells[2].Value.ToString();
            nv_dc.Text = nv_data.CurrentRow.Cells[3].Value.ToString();
        }

        private void buttonX8_Click(object sender, EventArgs e)
        {
            kh_them.Enabled = false;
            for (int i = 0; i < kh_data.RowCount - 1; i++)
            {
                if (kh_mkh.Text.Equals(kh_data.Rows[i].Cells[0].Value.ToString()))
                {
                    MessageBox.Show("Mã khách hàng đã tồn tại");
                    return;
                }
            }
            if (kh_sdt.TextLength > 11)
            {
                MessageBox.Show("Số điện thoại chỉ có ít hơn hoặc bằng 11 số");
                return;
            }
            SqlCommand cmd = new SqlCommand("themkhachhang", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@MaKH", kh_mkh.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@Ten", kh_tkh.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@SDT", kh_sdt.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@DC", kh_dc.Text);
            cmd.Parameters.Add(p);

            int count = cmd.ExecuteNonQuery();
            if (count > 0)
            {
                Open();
                MessageBox.Show("Them thanh cong");
            }

            ClearKH();
        }

        private void buttonX7_Click(object sender, EventArgs e)
        {
            if (kh_sdt.TextLength > 11)
            {
                MessageBox.Show("Số điện thoại chỉ có ít hơn hoặc bằng 11 số");
                return;
            }
            SqlCommand cmd = new SqlCommand("suakhachhang", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@MaKH", kh_mkh.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@Ten", kh_tkh.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@SDT", kh_sdt.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@DC", kh_dc.Text);
            cmd.Parameters.Add(p);

            int count = cmd.ExecuteNonQuery();
            if (count > 0)
            {
                Open();
                MessageBox.Show("Sua thanh cong");
            }

            ClearKH();
        }

        private void buttonX6_Click(object sender, EventArgs e)
        {
            SqlCommand cmd = new SqlCommand("xoaKH", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@MaKH", kh_mkh.Text);
            cmd.Parameters.Add(p);

            int count = cmd.ExecuteNonQuery();
            if (count > 0)
            {
                Open();
                MessageBox.Show("Xoa thanh cong");
            }

            ClearKH();
        }

        private void kh_data_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            kh_them.Enabled = false;
            kh_mkh.Text = kh_data.CurrentRow.Cells[0].Value.ToString();
            kh_tkh.Text = kh_data.CurrentRow.Cells[1].Value.ToString();
            kh_sdt.Text = kh_data.CurrentRow.Cells[2].Value.ToString();
            kh_dc.Text = kh_data.CurrentRow.Cells[3].Value.ToString();
        }

        private void buttonX12_Click(object sender, EventArgs e)
        {
            
            for (int i = 0; i < bh_data.RowCount; i++)
            {
                if (bh_mh.Text==bh_data.Rows[i].Cells["MaHang"].Value.ToString())
                {
                    if (bh_mp.Text == bh_data.Rows[i].Cells["MaPhieu"].Value.ToString())
                    {
                        MessageBox.Show("Mã hàng này đã tồn tại trong phiếu xuất này!");
                        return;
                    }
                }
                
            }
            

            bh_kh.Enabled = false;
         
            SqlCommand cmd = new SqlCommand("xuathangpx", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@MaPhieu", bh_mp.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaKH", bh_kh.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaNV", manv);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@NgayXuat", bh_nb.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaHang", bh_mh.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@SoLuong", bh_sl.Text);
            cmd.Parameters.Add(p);

            try
            {
                int count = cmd.ExecuteNonQuery();
                if (count > 0)
                {
                    Open();
                    MessageBox.Show("Them thanh cong");
                }
            }
            catch(Exception)
            {
                MessageBox.Show("Số lượng hàng trong kho không đủ để bán");
                return;
            }
            for (int i = 0; i < bh_data.Rows.Count; i++)
                if (bh_data.Rows[i].Cells["MaPhieu"].Value.ToString() == bh_mp.Text)
                {
                    bh_tgtx.Text = bh_data.Rows[i].Cells["TongGTX"].Value.ToString();
                    bh_ck.Text = bh_data.Rows[i].Cells["CK"].Value.ToString();
                    bh_tongtien.Text = bh_data.Rows[i].Cells["TongTien"].Value.ToString();
                    break;
                }
            ClearBH();
            dvbh.RowFilter = string.Format(@"MaPhieu like '%{0}%'", bh_mp.Text);
            bh_data.DataSource = dvbh;
            
        }

        private void bh_data_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            bh_them.Enabled = false;
            bh_kh.Enabled = true;
            bh_tgtx.Enabled = false;

           bh_ck.Text = bh_data.CurrentRow.Cells["CK"].Value.ToString();
           bh_tongtien.Text = bh_data.CurrentRow.Cells["TongTien"].Value.ToString();
           bh_mh.Text = bh_data.CurrentRow.Cells["MaHang"].Value.ToString();
           bh_th.Text = bh_data.CurrentRow.Cells["TenHang"].Value.ToString();
           bh_gb.Text = bh_data.CurrentRow.Cells["DonGia"].Value.ToString();
           bh_dv.Text = bh_data.CurrentRow.Cells["DonVi"].Value.ToString();      
           bh_sl.Text = bh_data.CurrentRow.Cells["SoLuong"].Value.ToString();
           bh_mp.Text = bh_data.CurrentRow.Cells["MaPhieu"].Value.ToString();
           bh_kh.Text = bh_data.CurrentRow.Cells["MaKH"].Value.ToString();
           bh_tgtx.Text = bh_data.CurrentRow.Cells["TongGTX"].Value.ToString(); 
           bh_nb.Value = Convert.ToDateTime(bh_data.CurrentRow.Cells["NgayXuat"].Value);
           bh_tt.Text = bh_data.CurrentRow.Cells["ThanhTien"].Value.ToString(); 

        }
        public int TimMa(string _Ma)
        {
            int i;
            for (i = 0; i < bh_data.Rows.Count; i++)
                if (bh_data.Rows[i].Cells["MaPhieu"].Value.ToString() ==_Ma)
                {
                    return i;
                }
            return -1;
        }
        public int TimMaPN(string _Ma)
        {
            int i;
            for (i = 0; i < nh_data.Rows.Count; i++)
                if (nh_data.Rows[i].Cells["MaPhieuNhap"].Value.ToString() == _Ma)
                {
                    return i;
                }
            return -1;
        }
        private void bh_tao_Click(object sender, EventArgs e)
        {
            ClearBH();
            for (int i = 0; i <= bh_data.Rows.Count; i++)
            {
                string s = "MPX" + (i + 1);
                if (TimMa(s) == -1)
                {
                    bh_mp.Text = s;
                    break;
                }
            }
            bh_kh.Enabled = true;
            bh_them.Enabled = true;
            bh_kh.Clear();
            bh_tgtx.Clear();
            bh_ck.Clear();
            bh_tongtien.Clear();
        }

        private void bh_sua_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < bh_data.RowCount; i++)
            {
                if (bh_mh.Text == bh_data.Rows[i].Cells["MaHang"].Value.ToString())
                {
                    if (bh_mp.Text == bh_data.Rows[i].Cells["MaPhieu"].Value.ToString())
                    {
                        MessageBox.Show("Mã hàng này đã tồn tại trong phiếu xuất này!");
                        return;
                    }
                }
            }
            bh_kh.Enabled = true;

            SqlCommand cmd = new SqlCommand("suaxuathang", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@MaPhieu", bh_mp.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaKH", bh_kh.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaNV", manv);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@NgayXuat", bh_nb.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaHang", bh_mh.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@SoLuong", bh_sl.Text);
            cmd.Parameters.Add(p);

            int count = cmd.ExecuteNonQuery();
            if (count > 0)
            {
                Open();
                MessageBox.Show("Sua thanh cong");
            }
        }

        private void bh_xoa_Click(object sender, EventArgs e)
        {
            SqlCommand cmd = new SqlCommand("xoaxuathang", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@MaHang", bh_mh.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaPhieu", bh_mp.Text);
            cmd.Parameters.Add(p);

            int count = cmd.ExecuteNonQuery();
            if (count > 0)
            {
                Open();
                MessageBox.Show("Xoa thanh cong");
            }

            ClearBH();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            nh_ncc.Enabled = true;
            nh_k.Enabled = true;
            ClearNH();
            nh_k.Text = "";
            nh_mp.Clear();
            nh_ncc.Text = "";
            nh_tonggt.Clear();
            bh_th.Enabled = false;
            for (int i = 0; i <= nh_data.Rows.Count; i++)
            {
                string s = "MPN" + (i + 1);
                if (TimMaPN(s) == -1)
                {
                    nh_mp.Text = s;
                    break;
                }
            }

            nh_mh.Enabled = true;
            nh_them.Enabled = true;
        }

        private void nh_data_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            nh_them.Enabled = false;
            nh_ncc.Enabled = true;
            nh_k.Enabled = true;

            nh_mh.Text = nh_data.CurrentRow.Cells["MaHangNhap"].Value.ToString();
            nh_th.Text = nh_data.CurrentRow.Cells["TenHangNhap"].Value.ToString();
            nh_gn.Text = nh_data.CurrentRow.Cells["GiaNhap"].Value.ToString();
            nh_dv.Text = nh_data.CurrentRow.Cells["DonViNhap"].Value.ToString();
            nh_sl.Text = nh_data.CurrentRow.Cells["SoLuongNhap"].Value.ToString();
            nh_tt.Text = nh_data.CurrentRow.Cells["ThanhTienNhap"].Value.ToString();
            nh_mp.Text = nh_data.CurrentRow.Cells["MaPhieuNhap"].Value.ToString();
            nh_ncc.Text = nh_data.CurrentRow.Cells["MaNCC"].Value.ToString();
            nh_nn.Value = Convert.ToDateTime(nh_data.CurrentRow.Cells["NgayNhap"].Value);
            nh_tonggt.Text = nh_data.CurrentRow.Cells["TongGTN"].Value.ToString();
        }

        public int TimMaHang(string _Ma)
        {
            int i;
            for (i = 0; i < hh_data.Rows.Count; i++)
                if (hh_data.Rows[i].Cells["MaHangHoa"].Value.ToString() == _Ma)
                {
                    return i;
                }
            return -1;
        }

        private void nh_thm_Click(object sender, EventArgs e)
        {
            nh_th.Enabled = true;
            nh_dv.Enabled = true;
            nh_sl.Enabled = true;
            for (int i = 0; i <= hh_data.Rows.Count; i++)
            {
                string s = "MH" + (i + 1);
                if (TimMaHang(s) == -1)
                {
                    nh_mh.Text = s;
                    break;
                }
            }
            nh_mh.Enabled = false;
            nh_th.Enabled = true;
            nh_dv.Enabled = true;
        }

        private void nh_sua_Click(object sender, EventArgs e)
        {
         
            nh_ncc.Enabled = false;
            nh_k.Enabled = false;

            SqlCommand cmd = new SqlCommand("suanhaphang", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@MaPhieu", nh_mp.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaDV", nh_ncc.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaNV", manv);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@NgayNhap", nh_nn.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaHang", nh_mh.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@SoLuong", nh_sl.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@DonGia", nh_gn.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@Kho", nh_k.Text);
            cmd.Parameters.Add(p);

            int count = cmd.ExecuteNonQuery();
            if (count > 0)
            {
                Open();
                MessageBox.Show("Sua thanh cong");
            }
        }

        private void nh_xoa_Click(object sender, EventArgs e)
        {
            SqlCommand cmd = new SqlCommand("xoanhaphang", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@MaHang", nh_mh.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaPhieu", nh_mp.Text);
            cmd.Parameters.Add(p);

            int count = cmd.ExecuteNonQuery();
            if (count > 0)
            {
                Open();
                MessageBox.Show("Xoa thanh cong");
            }

            ClearNH();
        }

        private void ncc_sua_Click(object sender, EventArgs e)
        {
            if (ncc_SDT.TextLength > 11)
            {
                MessageBox.Show("Số điện thoại chỉ có ít hơn hoặc bằng 11 số");
                return;
            }
            SqlCommand cmd = new SqlCommand("SuaNCC", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@MaDV", ncc_MaDV.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@TenDV", ncc_TenDV.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@SDT", ncc_SDT.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@DC", ncc_DC.Text);
            cmd.Parameters.Add(p);

            int count = cmd.ExecuteNonQuery();
            if (count > 0)
            {
                Open();
                MessageBox.Show("Sua thanh cong");
            }

            ClearNCC();
        }

        private void ncc_xoa_Click(object sender, EventArgs e)
        {
            SqlCommand cmd = new SqlCommand("xoaNCC", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@MaDV", ncc_MaDV.Text);
            cmd.Parameters.Add(p);

            int count = cmd.ExecuteNonQuery();
            if (count > 0)
            {
                Open();
                MessageBox.Show("Xoa thanh cong");
            }

            ClearNCC();
        }

        private void ncc_data_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            ncc_them.Enabled = false;
            ncc_MaDV.Text = ncc_data.CurrentRow.Cells[0].Value.ToString();
            ncc_TenDV.Text = ncc_data.CurrentRow.Cells[1].Value.ToString();
            ncc_SDT.Text = ncc_data.CurrentRow.Cells[2].Value.ToString();
            ncc_DC.Text = ncc_data.CurrentRow.Cells[3].Value.ToString();
        }

        public int TimNCC(string _Ma)
        {
            int i;
            for (i = 0; i < ncc_data.Rows.Count; i++)
                if (ncc_data.Rows[i].Cells["MaDV2"].Value.ToString() == _Ma)
                {
                    return i;
                }
            return -1;
        }
        private void ncc_tao_Click(object sender, EventArgs e)
        {
            ClearNCC();
            for (int i = 0; i <= ncc_data.Rows.Count; i++)
            {
                string s = "DV" + (i + 1);
                if (TimNCC(s) == -1)
                {
                    ncc_MaDV.Text = s;
                    break;
                }
            }

            ncc_them.Enabled = true;
        }

        public int Timk(string _Ma)
        {
            int i;
            for (i = 0; i < k_data.Rows.Count; i++)
                if (k_data.Rows[i].Cells["KS"].Value.ToString() == _Ma)
                {
                    return i;
                }
            return -1;
        }
        private void k_tao_Click(object sender, EventArgs e)
        {
            ClearK();
            for (int i = 0; i <= k_data.Rows.Count; i++)
            {
                string s = "" + (i + 1);
                if (Timk(s) == -1)
                {
                    k_k.Text = s;
                    break;
                }
            }

            k_them.Enabled = true;
        }

        public int Timnv(string _Ma)
        {
            int i;
            for (i = 0; i < nv_data.Rows.Count; i++)
                if (nv_data.Rows[i].Cells[0].Value.ToString() == _Ma)
                {
                    return i;
                }
            return -1;
        }
        private void nv_tao_Click(object sender, EventArgs e)
        {
            ClearNV();
            for (int i = 0; i <= nv_data.Rows.Count; i++)
            {
                string s = "NV" + (i + 1);
                if (Timnv(s) == -1)
                {
                    nv_mnv.Text = s;
                    break;
                }
            }

            nv_them.Enabled = true;
        }

        public int Timkh(string _Ma)
        {
            int i;
            for (i = 0; i < kh_data.Rows.Count; i++)
                if (kh_data.Rows[i].Cells[0].Value.ToString() == _Ma)
                {
                    return i;
                }
            return -1;
        }
        private void kh_tao_Click(object sender, EventArgs e)
        {
            ClearKH();
            for (int i = 0; i <= kh_data.Rows.Count; i++)
            {
                string s = "KH" + (i + 1);
                if (Timkh(s) == -1)
                {
                    kh_mkh.Text = s;
                    break;
                }
            }

            kh_them.Enabled = true;
        }

        private void button1_Click_1(object sender, EventArgs e)
        {
            if (bc_date2.Visible == false)
            {
                DateTime NX = new DateTime();
                NX = bc_date1.Value;
                string s = string.Format("Select NgayXuat,MaPhieu,TongGTX,MaKH, 'lai'=dbo.fun_laingay('{0}') From PhieuXuat where PhieuXuat.NgayXuat='{0}'", NX.Date.ToShortDateString());
                SqlDataAdapter databc = new SqlDataAdapter(s, conn);
                DataTable dtbc = new DataTable();
                databc.Fill(dtbc);
                dvbc = new DataView(dtbc);
                bc_data.DataSource = dvbc;

                if (dtbc.Rows.Count > 0)
                bc_DT.Text = bc_data.Rows[0].Cells["lai"].Value.ToString();

            }
            else
            {
                DateTime NX1 = new DateTime();
                DateTime NX2 = new DateTime();
                NX1 = bc_date1.Value;
                NX2 = bc_date2.Value;
                string s = string.Format("Select MaPhieu,TongGTX,MaKH, 'lai'=dbo.fun_laiktg('{0}','{1}'),NgayXuat From PhieuXuat where PhieuXuat.NgayXuat<='{1}'and PhieuXuat.NgayXuat>='{0}'", NX1.Date.ToShortDateString(),NX2.Date.ToShortDateString());
                SqlDataAdapter databc = new SqlDataAdapter(s, conn);
                DataTable dtbc = new DataTable();
                databc.Fill(dtbc);
                dvbc = new DataView(dtbc);
                bc_data.DataSource = dvbc;
                if(dtbc.Rows.Count>0)
                bc_DT.Text = bc_data.Rows[0].Cells["lai"].Value.ToString();
            }
        }

        private void bc_time_SelectedIndexChanged(object sender, EventArgs e)
        {
        {
            if (bc_time.SelectedIndex == 1)
            {
                bc_tl.Enabled = true;
                bc_date1.Enabled = true;
                bc_date2.Visible = true;
            }
            else
            {
                bc_date2.Visible = false;
                bc_tl.Enabled = true;
                bc_date1.Enabled = true;
            } 
        }

        }

        private void ncc_them_Click(object sender, EventArgs e)
        {
            ncc_them.Enabled = false;
            for (int i = 0; i < ncc_data.RowCount - 1; i++)
            {
                if (ncc_MaDV.Text.Equals(ncc_data.Rows[i].Cells[0].Value.ToString()))
                {
                    MessageBox.Show("Mã Đơn Vị Đã Tồn Tại!");
                    return;
                }
            }
            if (ncc_SDT.TextLength > 11)
            {
                MessageBox.Show("Số Điện thoại có ít hơn hoặc bằng 11 số!");
                return;
            }
            SqlCommand cmd = new SqlCommand("ThemNCC", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@MaDV", ncc_MaDV.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@TenDV", ncc_TenDV.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@SDT", ncc_SDT.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@DC", ncc_DC.Text);
            cmd.Parameters.Add(p);

            int count = cmd.ExecuteNonQuery();
            if (count > 0)
            {
                Open();
                MessageBox.Show("Them thanh cong");
            }

            ClearNCC();

        }

        private void tk_mh_TextChanged(object sender, EventArgs e)
        {
            dvhh.RowFilter = string.Format(@"MaHang like '%{0}%'", tk_mh.Text);
        }

        private void tk_th_TextChanged(object sender, EventArgs e)
        {
            dvhh.RowFilter = string.Format(@"TenHang like '%{0}%'", tk_th.Text);
        }

        private void tk_dv_TextChanged(object sender, EventArgs e)
        {
            dvhh.RowFilter = string.Format(@"DonVi like '%{0}%'", tk_dv.Text);
        }

        private void tk_sl1_TextChanged(object sender, EventArgs e)
        {
            if (tk_sl1.Text == "")
            {
                dvhh.RowFilter = string.Format(@"SoLuong > -1");
            }
            else
            {
                int sl = Convert.ToInt16(tk_sl1.Text);
                dvhh.RowFilter = string.Format(@"SoLuong = {0}", sl);
            }
        }

        private void tk_gn2_TextChanged(object sender, EventArgs e)
        {
            if (tk_gn2.Text == "")
            {
                dvhh.RowFilter = string.Format(@"GiaNhap >-1");
            }
            else
            {
                int s = Convert.ToInt32(tk_gn2.Text);
                dvhh.RowFilter = string.Format(@"GiaNhap = {0}", s);
            }

        }

        private void tk_gb1_TextChanged(object sender, EventArgs e)
        {
            
            if (tk_gb1.Text == "")
            {
                dvhh.RowFilter = string.Format(@"SoLuong > -1");
            }
            else
            {
                int gb = Convert.ToInt32(tk_gb1.Text);
                dvhh.RowFilter = string.Format(@"SoLuong > {0}", gb);
            }

        }

        private void tk_ncc_TextChanged(object sender, EventArgs e)
        {
            dvhh.RowFilter = string.Format(@"TenDV like '%{0}%'", tk_ncc.Text);
        }

        private void tk_k_TextChanged(object sender, EventArgs e)
        {
         dvhh.RowFilter = string.Format(@"DiaDiem like '%{0}%'", tk_k.Text);
        }

        public static void ExportDataGridViewTo_Excel12(DataGridView myDataGridViewQuantity)
        {
            Excel_12.Application oExcel_12 = null; //Excel_12 Application 
            Excel_12.Workbook oBook = null; // Excel_12 Workbook 
            Excel_12.Sheets oSheetsColl = null; // Excel_12 Worksheets collection 
            Excel_12.Worksheet oSheet = null; // Excel_12 Worksheet 
            Excel_12.Range oRange = null; // Cell or Range in worksheet 

            Object oMissing = System.Reflection.Missing.Value;
            // Create an instance of Excel_12. 
            oExcel_12 = new Excel_12.Application();
            // Make Excel_12 visible to the user. 
            oExcel_12.Visible = true;
            // Set the UserControl property so Excel_12 won't shut down. 
            oExcel_12.UserControl = true;
            // System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-US"); 
            //object file = File_Name;
            //object missing = System.Reflection.Missing.Value;
            // Add a workbook. 
            oBook = oExcel_12.Workbooks.Add(oMissing);
            // Get worksheets collection 
            oSheetsColl = oExcel_12.Worksheets;
            // Get Worksheet "Sheet1" 
            oSheet = (Excel_12.Worksheet)oSheetsColl.get_Item("Sheet1");
            oSheet.Name = "HangHoa";
            // Export titles 
            for (int j = 0; j < myDataGridViewQuantity.Columns.Count; j++)
            {
                oRange = (Excel_12.Range)oSheet.Cells[1, j + 1];
                oRange.Value2 = myDataGridViewQuantity.Columns[j].HeaderText;
            }
            // Export data 
            for (int i = 0; i < myDataGridViewQuantity.Rows.Count; i++)
            {
                for (int j = 0; j < myDataGridViewQuantity.Columns.Count; j++)
                {
                    oRange = (Excel_12.Range)oSheet.Cells[i + 2, j + 1];

                    oRange.Value2 = myDataGridViewQuantity[j, i].Value;
                }
            }
            oBook = null;
            oExcel_12.Quit();
            oExcel_12 = null;
            GC.Collect();
        }
        private void button1_Click_2(object sender, EventArgs e)
        {
            try
            {
                ExportDataGridViewTo_Excel12(bc_data);
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message.ToString());
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                ExportDataGridViewTo_Excel12(hh_data);
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message.ToString());
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            ClearHDBH();
            for (int i = 0; i <= bh_data.Rows.Count; i++)
            {
                string s = "MPX" + (i + 1);
                if (TimMa(s) == -1)
                {
                    hdbh_mp.Text = s;
                    break;
                }
            }
            hdbh_kh.Enabled = true;
            hdbh_them.Enabled = true;
            hdbh_kh.Clear();
            hdbh_tgtx.Clear();
            hdbh_ck.Clear();
            hdbh_tongtien.Clear();
        }

        private void buttonX9_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < lsv_bh.Items.Count; i++)
            {
                if (hdbh_mh.Text == lsv_bh.Items[0].Text)
                {
                    MessageBox.Show("Da ton tai ma hang nay");
                    return;
                }
            }

            SqlCommand cmd = new SqlCommand("xuathangpx", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@MaPhieu", hdbh_mp.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaKH", hdbh_kh.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaNV", manv);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@NgayXuat", hdbh_nb.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaHang", hdbh_mh.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@SoLuong", hdbh_sl.Text);
            cmd.Parameters.Add(p);

            try
            {
                int count = cmd.ExecuteNonQuery();
                if (count > 0)
                {
                    Open();
                    MessageBox.Show("Them thanh cong");
                }
            }
            catch (Exception)
            {
                MessageBox.Show("Số lượng hàng trong kho không đủ để bán");
                return;
            }

            string[] arr = new string[6];
            arr[0] = hdbh_mh.Text;
            arr[1] = hdbh_th.Text;
            arr[2] = hdbh_dv.Text;
            arr[3] = hdbh_sl.Text;
            arr[4] = hdbh_dg.Text;
            arr[5] = hdbh_tt.Text;

            ListViewItem items = new ListViewItem(arr);
            lsv_bh.Items.Add(items);
            for (int i = 0; i < bh_data.Rows.Count; i++)
                if (bh_data.Rows[i].Cells["MaPhieu"].Value.ToString() == hdbh_mp.Text)
                {
                    hdbh_tgtx.Text = bh_data.Rows[i].Cells["TongGTX"].Value.ToString();
                    hdbh_ck.Text = bh_data.Rows[i].Cells["CK"].Value.ToString();
                    hdbh_tongtien.Text = bh_data.Rows[i].Cells["TongTien"].Value.ToString();
                    break;
                }
            ClearHDBH();
        }

        private void buttonX11_Click(object sender, EventArgs e)
        {
            for (int i = lsv_bh.Items.Count-1; i >= 0; i--)
            {
                lsv_bh.Items[i].Remove();
            }
                MessageBox.Show("Ket thuc!");
        }

        private void nh_th_TextChanged(object sender, EventArgs e)
        {
            for (int i = 0; i <= hh_data.RowCount - 1; i++)
            {
                if (hh_data.Rows[i].Cells[1].Value.ToString().ToLower().CompareTo(nh_th.Text.ToLower()) == 0)
                {
                    nh_gn.Text = hh_data.Rows[i].Cells[4].Value.ToString();
                    nh_dv.Text = hh_data.Rows[i].Cells[2].Value.ToString();
                    nh_sl.Text = hh_data.Rows[i].Cells[3].Value.ToString();
                    nh_mh.Text = hh_data.Rows[i].Cells[0].Value.ToString();
                    break;
                }
                else
                {
                    nh_gn.Clear();
                    nh_dv.ResetText();
                    nh_sl.Clear();
                }
            }
        }

        private void bh_th_TextChanged(object sender, EventArgs e)
        {
            for (int i = 0; i <= hh_data.RowCount - 1; i++)
            {
                if (hh_data.Rows[i].Cells[1].Value.ToString().ToLower().CompareTo(bh_th.Text.ToLower()) == 0)
                {
                    bh_gb.Text = hh_data.Rows[i].Cells[5].Value.ToString();
                    bh_dv.Text = hh_data.Rows[i].Cells[2].Value.ToString();
                    bh_sl.Text = hh_data.Rows[i].Cells[3].Value.ToString();
                    bh_mh.Text = hh_data.Rows[i].Cells[0].Value.ToString();
                    break;
                }
                else
                {
                    bh_gb.Clear();
                    bh_dv.ResetText();
                    bh_sl.Clear();
                }
            }
        }

        private void hdbh_th_TextChanged(object sender, EventArgs e)
        {
            for (int i = 0; i <= hh_data.RowCount - 1; i++)
            {
                if (hh_data.Rows[i].Cells[1].Value.ToString().ToLower().CompareTo(hdbh_th.Text.ToLower()) == 0)
                {
                    hdbh_dg.Text = hh_data.Rows[i].Cells[5].Value.ToString();
                    hdbh_dv.Text = hh_data.Rows[i].Cells[2].Value.ToString();
                    hdbh_sltk.Text = hh_data.Rows[i].Cells[3].Value.ToString();
                    hdbh_mh.Text = hh_data.Rows[i].Cells[0].Value.ToString();
                    break;
                }
                else
                {
                    hdbh_dg.Clear();
                    hdbh_dv.ResetText();
                    hdbh_sltk.Clear();
                }
            }
        }

        private void hdbh_sl_TextChanged(object sender, EventArgs e)
        {
            if (hdbh_sl.Text == "") hdbh_sl.Text = "0";
            if (hdbh_sl.Text != "0")
            {
                int sl = Convert.ToInt32(hdbh_sl.Text);
                int sltk = Convert.ToInt32(hdbh_sltk.Text);

                hdbh_tt.Text = (Convert.ToDouble(hdbh_dg.Text) * sl).ToString();
                if (sl > sltk)
                {
                    MessageBox.Show("SL vuot qua so luong trong kho");
                    hdbh_sl.Text = "";
                    return;
                }
            }
        }

        private void lsv_bh_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (lsv_bh.Items.Count > 0 && lsv_bh.SelectedItems.Count > 0)
            {
                hdbh_mh.Text = lsv_bh.SelectedItems[0].SubItems[0].Text;
                hdbh_th.Text = lsv_bh.SelectedItems[0].SubItems[1].Text;
                hdbh_dv.Text = lsv_bh.SelectedItems[0].SubItems[2].Text;
                hdbh_sl.Text = lsv_bh.SelectedItems[0].SubItems[3].Text;
                hdbh_dg.Text = lsv_bh.SelectedItems[0].SubItems[4].Text;
                hdbh_tt.Text = lsv_bh.SelectedItems[0].SubItems[5].Text;
            }        
        }

        private void hdbh_sua_Click(object sender, EventArgs e)
        {

            SqlCommand cmd = new SqlCommand("suaxuathang", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@MaPhieu", hdbh_mp.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaKH", hdbh_kh.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaNV", manv);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@NgayXuat", hdbh_nb.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaHang", hdbh_mh.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@SoLuong", hdbh_sl.Text);
            cmd.Parameters.Add(p);

            int count = cmd.ExecuteNonQuery();
            if (count > 0)
            {
                Open();
                MessageBox.Show("Sua thanh cong");
                string[] arr = new string[6];
                arr[0] = hdbh_mh.Text;
                arr[1] = hdbh_th.Text;
                arr[2] = hdbh_dv.Text;
                arr[3] = hdbh_sl.Text;
                arr[4] = hdbh_dg.Text;
                arr[5] = hdbh_tt.Text;
                int i = 0;
                while (lsv_bh.Items[i].SubItems[0].Text != hdbh_mh.Text) i++;

                lsv_bh.Items[i] = new ListViewItem(arr);
                lsv_bh.Invalidate();
            }
            for (int i = 0; i < bh_data.Rows.Count; i++)
                if (bh_data.Rows[i].Cells["MaPhieu"].Value.ToString() == hdbh_mp.Text)
                {
                    hdbh_tgtx.Text = bh_data.Rows[i].Cells["TongGTX"].Value.ToString();
                    hdbh_ck.Text = bh_data.Rows[i].Cells["CK"].Value.ToString();
                    hdbh_tongtien.Text = bh_data.Rows[i].Cells["TongTien"].Value.ToString();
                    break;
                }
            ClearHDBH();
        }

        private void superTabControlPanel3_Click(object sender, EventArgs e)
        {

        }

        private void hdbh_xoa_Click(object sender, EventArgs e)
        {
            if (lsv_bh.SelectedItems.Count > 0)
            {
                lsv_bh.SelectedItems[0].Remove();
            }
            else
            {
                MessageBox.Show("Bạn cần chọn một sản phẩm để xoá!");
                return;
            }

            SqlCommand cmd = new SqlCommand("xoaxuathang", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@MaHang", hdbh_mh.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaPhieu", hdbh_mp.Text);
            cmd.Parameters.Add(p);

            int count = cmd.ExecuteNonQuery();
            if (count > 0)
            {
                Open();
                MessageBox.Show("Xoa thanh cong");
            }
            for (int i = 0; i < bh_data.Rows.Count; i++)
                if (bh_data.Rows[i].Cells["MaPhieu"].Value.ToString() == hdbh_mp.Text)
                {
                    hdbh_tgtx.Text = bh_data.Rows[i].Cells["TongGTX"].Value.ToString();
                    hdbh_ck.Text = bh_data.Rows[i].Cells["CK"].Value.ToString();
                    hdbh_tongtien.Text = bh_data.Rows[i].Cells["TongTien"].Value.ToString();
                    break;
                }
            ClearHDBH();
        }

        private void hdnh_tao_Click(object sender, EventArgs e)
        {
            hdnh_ncc.Enabled = true;
            hdnh_k.Enabled = true;
            ClearHDNH();
            hdnh_k.Text = "";
            hdnh_mp.Clear();
            hdnh_ncc.Text = "";
            hdnh_tgtn.Clear();
            
            for (int i = 0; i <= nh_data.Rows.Count; i++)
            {
                string s = "MPN" + (i + 1);
                if (TimMaPN(s) == -1)
                {
                    hdnh_mp.Text = s;
                    break;
                }
            }
        }

        private void hdbh_in_Click(object sender, EventArgs e)
        {

        }

        private void hdnh_thm_Click(object sender, EventArgs e)
        {
            ClearHDNH();
            for (int i = 0; i <= hh_data.Rows.Count; i++)
            {
                string s = "MH" + (i + 1);
                if (TimMaHang(s) == -1)
                {
                    hdnh_mh.Text = s;
                    break;
                }
            }
            
        }

        private void hdnh_sl_TextChanged(object sender, EventArgs e)
        {
            if (hdnh_sl.Text == "") hdnh_sl.Text = "0";
            if (hdnh_sl.Text != "0" && hdnh_gn.Text!="")
            {
                int sl = Convert.ToInt32(hdnh_sl.Text);

                hdnh_tt.Text = (Convert.ToDouble(hdnh_gn.Text) * sl).ToString();

            }
        }

        private void hdnh_th_TextChanged(object sender, EventArgs e)
        {
            for (int i = 0; i <= hh_data.RowCount - 1; i++)
            {
                if (hh_data.Rows[i].Cells[1].Value.ToString().ToLower().CompareTo(hdnh_th.Text.ToLower()) == 0)
                {
                    hdnh_gn.Text = hh_data.Rows[i].Cells[4].Value.ToString();
                    hdnh_dv.Text = hh_data.Rows[i].Cells[2].Value.ToString();
                    hdnh_mh.Text = hh_data.Rows[i].Cells[0].Value.ToString();
                    break;
                }
                else
                {
                    hdnh_gn.Clear();
                    hdnh_dv.ResetText();
                    hdnh_sl.Clear();
                }
            }
        }

        private void hdnh_them_Click(object sender, EventArgs e)
        {

            for (int i = 0; i < lsv_nh.Items.Count; i++)
            {
                if (hdnh_mh.Text == lsv_nh.Items[0].Text)
                {
                    MessageBox.Show("Da ton tai ma hang nay");
                    return;
                }
            }

            SqlCommand cmd = new SqlCommand("nhaphang", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@MaPhieu", hdnh_mp.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaDV", hdnh_ncc.SelectedValue.ToString());
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaNV", manv);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@NgayNhap", hdnh_nn.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaHang", hdnh_mh.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@SoLuong", hdnh_sl.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@DonGia", hdnh_gn.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@Kho", hdnh_k.SelectedValue);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@TenHang", hdnh_th.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@DonVi", hdnh_dv.SelectedValue.ToString());
            cmd.Parameters.Add(p);
            p = new SqlParameter("@DVCC", hdnh_ncc.SelectedValue.ToString());
            cmd.Parameters.Add(p);

            try
            {
                int count = cmd.ExecuteNonQuery();
                if (count > 0)
                {
                    Open();
                    MessageBox.Show("Them thanh cong");
                }
            }
            catch (Exception)
            {
                MessageBox.Show("Khong thanh cong");
                return;
            }

            string[] arr = new string[6];
            arr[0] = hdnh_mh.Text;
            arr[1] = hdnh_th.Text;
            arr[2] = hdnh_dv.SelectedValue.ToString();
            arr[3] = hdnh_sl.Text;
            arr[4] = hdnh_gn.Text;
            arr[5] = hdnh_tt.Text;

            ListViewItem items = new ListViewItem(arr);
            lsv_nh.Items.Add(items);
            for (int i = 0; i < nh_data.Rows.Count; i++)
                if (nh_data.Rows[i].Cells["MaPhieuNhap"].Value.ToString() == hdnh_mp.Text)
                {
                    hdnh_tgtn.Text = nh_data.Rows[i].Cells["TongGTN"].Value.ToString();
                    break;
                }
            ClearHDNH();
        }

        private void buttonX5_Click_1(object sender, EventArgs e)
        {
            for (int i = lsv_nh.Items.Count - 1; i >= 0; i--)
            {
                lsv_nh.Items[i].Remove();
            }
            MessageBox.Show("Ket thuc!");
        }

        private void hdnh_sua_Click(object sender, EventArgs e)
        {
            SqlCommand cmd = new SqlCommand("suanhaphang", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@MaPhieu", nh_mp.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaDV", nh_ncc.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaNV", manv);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@NgayNhap", nh_nn.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaHang", nh_mh.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@SoLuong", nh_sl.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@DonGia", nh_gn.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@Kho", nh_k.Text);
            cmd.Parameters.Add(p);

            int count = cmd.ExecuteNonQuery();
            if (count > 0)
            {
                Open();
                MessageBox.Show("Sua thanh cong");
                string[] arr = new string[6];
                arr[0] = hdnh_mh.Text;
                arr[1] = hdnh_th.Text;
                arr[2] = hdnh_dv.Text;
                arr[3] = hdnh_sl.Text;
                arr[4] = hdnh_gn.Text;
                arr[5] = hdnh_tt.Text;
                int i = 0;
                while (lsv_nh.Items[i].SubItems[0].Text != hdnh_mh.Text) i++;

                lsv_nh.Items[i] = new ListViewItem(arr);
                lsv_nh.Invalidate();
            }
            for (int i = 0; i < nh_data.Rows.Count; i++)
                if (nh_data.Rows[i].Cells["MaPhieuNhap"].Value.ToString() == hdnh_mp.Text)
                {
                    hdnh_tgtn.Text = nh_data.Rows[i].Cells["TongGTN"].Value.ToString();
                    break;
                }
            ClearHDNH();
        }

        private void lsv_nh_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (lsv_nh.Items.Count > 0 && lsv_nh.SelectedItems.Count > 0)
            {
                hdnh_mh.Text = lsv_nh.SelectedItems[0].SubItems[0].Text;
                hdnh_th.Text = lsv_nh.SelectedItems[0].SubItems[1].Text;
                hdnh_dv.Text = lsv_nh.SelectedItems[0].SubItems[2].Text;
                hdnh_sl.Text = lsv_nh.SelectedItems[0].SubItems[3].Text;
                hdnh_gn.Text = lsv_nh.SelectedItems[0].SubItems[4].Text;
                hdnh_tt.Text = lsv_nh.SelectedItems[0].SubItems[5].Text;
            }       
        }

        private void hdnh_xoa_Click(object sender, EventArgs e)
        {

            if (lsv_nh.SelectedItems.Count > 0)
            {
                lsv_nh.SelectedItems[0].Remove();
            }
            else
            {
                MessageBox.Show("Bạn cần chọn một sản phẩm để xoá!");
                return;
            }

            SqlCommand cmd = new SqlCommand("xoanhaphang", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@MaHang", hdnh_mh.Text);
            cmd.Parameters.Add(p);
            p = new SqlParameter("@MaPhieu", hdnh_mp.Text);
            cmd.Parameters.Add(p);

            int count = cmd.ExecuteNonQuery();
            if (count > 0)
            {
                Open();
                MessageBox.Show("Xoa thanh cong");
            }

            for (int i = 0; i < nh_data.Rows.Count; i++)
                if (nh_data.Rows[i].Cells["MaPhieuNhap"].Value.ToString() == hdnh_mp.Text)
                {
                    hdnh_tgtn.Text = nh_data.Rows[i].Cells["TongGTN"].Value.ToString();
                    break;
                }
        }
    }
}
